98

Beginner’s Guide to Code Algorithms

98

PickFile Initialize

Sub PickFile(FileName)

    Dim fldr As FileDialog

    Dim sItem As String

    Set fldr =​ Application.FileDialog(msoFileDialogSaveAs)

    With fldr

      .Title =​ “Save as”

      .InitialFileName =​ “test.xlsx”

      .FilterIndex =​ 1

      If .Show <> 0 Then

        FileName =​ .SelectedItems(1)

        ActiveWorkbook.SaveAs FileName:=​.SelectedItems(1)

      End If

    End With

End Sub

6.5  THE PICKFILE SUBROUTINE

The PickFile is a subroutine that is executed in step 1 to save the target file. This is

a technique to ensure that the target file is preserved, and the result of this program is

a copy of what was provided as the target file. This has a unique advantage. Through

this technique, you can do this process repetitively with the target file as one of the

sources. If you have many files to merge, it can take some time to complete the oper­

ation. Breaking it up into chunks of ten files helps to ensure you do not run out of

space and get an opportunity to save your work often.

A few more points about the PopulateRow subroutine.

A sheet is copied only if the sheet name matches the target. This avoids the issue

of having extra sheets in your sources that the user might have created for their own

calculation but is not relevant for your analysis. This is achieved by the statement

“For Each Sheet In thisWb.Worksheets”.

A piece of code needs to be called out for its ability to find the first blank row in

the source file. The reason it is elegant is because it is so brief –​

NumberOfRows1 =​ fileopenTargetWb.Sheets(Sheet.Name).Cells(Rows.

Count,FirstColumn).End(x1Up).Row

End(x1up) positions the cursor on the last non-​blank row.

6.6  CONCLUSION

Quite a few small techniques come in handy for a task as you try to automate it.

This chapter discusses a macro that is extremely useful in many business situations.

Although the task of copying cells to another sheet seems trivial, there are many intri­

cate details that you must consider to successfully automate this process.

Some unique items you learnt in this chapter are: